#! /bin/bash 
#SQOOP_HOME=/opt/cloudera/parcels/CDH-6.2.1-1.cdh6.2.1.p0.1425774/bin/sqoop
SQOOP_HOME=/usr/bin/sqoop
HIVE_HOME=/usr/bin/hive
if [[ $1 == "" ]];then
   dt=`date -d '1 days ago' "+%Y-%m-%d"`
else
   dt=$1
fi

echo '========================================'
echo '==============开始循环增量导入==============='
echo '========================================'
# 1.客户静态信息表
/usr/bin/sqoop import "-Dorg.apache.sqoop.splitter.allow_text_splitter=true" \
--connect 'jdbc:mysql://106.75.33.59/scrm?useUnicode=true&characterEncoding=UTF-8&autoReconnect=true' \
--username itcast_edu_stu \
--password itcast_edu_stu \
--query "select *, '${dt}' as dt from customer where 1=1 and (create_date_time between '${dt} 00:00:00' and '${dt} 23:59:59') and \$CONDITIONS" \
--hcatalog-database zx_ods \
--hcatalog-table ods_customer \
-m 1

# 2.线索申诉信息表
/usr/bin/sqoop import "-Dorg.apache.sqoop.sqlitter.allow_text_sqlitter=true" \
--connect 'jdbc:mysql://106.75.33.59:3306/scrm?useUnicode=true&characterEncoding=UTF8&autoReconnect=true' \
--username itcast_edu_stu \
--password itcast_edu_stu \
--query "select * ,'${dt}' as dt from customer_appeal where 1=1 and (create_date_time between '${dt} 00:00:00' and '${dt} 23:59:59') and \$CONDITIONS" \
--hcatalog-database zx_ods \
--hcatalog-table ods_customer_appeal_apply \
-m 1
wait

# 3.客户线索表
/usr/bin/sqoop import "-Dorg.apache.sqoop.sqlitter.allow_text_sqlitter=true" \
--connect 'jdbc:mysql://106.75.33.59:3306/scrm?useUnicode=true&characterEncoding=UTF8&autoReconnect=true' \
--username itcast_edu_stu \
--password itcast_edu_stu \
--query "select * ,'${dt}' as dt from customer_clue where 1=1 and (create_date_time between '${dt} 00:00:00' and '${dt} 23:59:59') and \$CONDITIONS" \
--hcatalog-database zx_ods \
--hcatalog-table ods_customer_clue_apply \
-m 1
wait

# 4.客户意向表
/usr/bin/sqoop import "-Dorg.apache.sqoop.sqlitter.allow_text_sqlitter=true" \
--connect 'jdbc:mysql://106.75.33.59:3306/scrm?useUnicode=true&characterEncoding=UTF8&autoReconnect=true' \
--username itcast_edu_stu \
--password itcast_edu_stu \
--query "select * ,'${dt}' as dt from customer_relationship where 1=1 and (create_date_time between '${dt} 00:00:00' and '${dt} 23:59:59') and \$CONDITIONS" \
--hcatalog-database zx_ods \
--hcatalog-table ods_customer_relationship_apply \
-m 1
wait

# 5.员工信息表
${HIVE_HOME} -S -e "drop table if exists zx_ods.ods_employee_apply;
create table if not exists zx_ods.ods_employee_apply
(
    id                  int comment "",
    email               string comment "公司邮箱/OA登录账号",
    real_name           string comment "员工的真实姓名",
    phone               string comment "手机号目前还没有使用；隐私问题OA接口没有提供这个属性",
    department_id       string comment "OA中的部门编号有负值",
    department_name     string comment "OA中的部门名",
    remote_login        int comment "员工是否可以远程登录",
    job_number          string comment "员工工号",
    cross_school        int comment "是否有跨校区权限",
    last_login_date     string comment "最后登录日期",
    creator             int comment "创建人",
    create_date_time    string comment "创建时间",
    update_date_time    string comment "最后更新时间",
    deleted             int comment "是否被删除（禁用）",
    scrm_department_id  int comment "内部部门id",
    leave_office        int comment "离职状态",
    leave_office_time   string comment "离职时间",
    reinstated_time     string comment "复职时间",
    superior_leaders_id int comment "上级领导ID",
    tdepart_id          int comment "直属部门",
    tenant              int comment "",
    ems_user_name       string comment ""
)
    row format delimited fields terminated by '\t'
    stored as orc tblproperties ("orc.compress" = "ZLIB")
;"

/usr/bin/sqoop import "-Dorg.apache.sqoop.sqlitter.allow_text_sqlitter=true" \
--connect 'jdbc:mysql://106.75.33.59:3306/scrm?useUnicode=true&characterEncoding=UTF8&autoReconnect=true' \
--username itcast_edu_stu \
--password itcast_edu_stu \
--query "select * from employee where 1=1 and \$CONDITIONS" \
--hcatalog-database zx_ods \
--hcatalog-table ods_employee_apply \
-m 1
wait

# 6.班级信息表
${HIVE_HOME} -S -e "drop table if exists zx_ods.ods_itcast_clazz_apply;
create table if not exists zx_ods.ods_itcast_clazz_apply
(
    id                  int comment "ems课程id(非自增)",
    create_date_time    string comment "创建时间,",
    update_date_time    string comment "最后更新时间,",
    deleted             int comment "是否被删除（禁用）,",
    itcast_school_id    string comment "ems校区ID,",
    itcast_school_name  string comment "ems校区名称,",
    itcast_subject_id   string comment "ems学科ID,",
    itcast_subject_name string comment "ems学科名称,",
    itcast_brand        string comment "ems品牌,",
    clazz_type_state    string comment "班级类型状态,",
    clazz_type_name     string comment "班级类型名称,",
    teaching_mode       string comment "授课模式,",
    start_time          string comment "开班时间,",
    end_time            string comment "毕业时间,",
    `comment`           string comment "备注,",
    detail              string comment "详情(比如：27期),",
    uncertain           int comment "待定班(0:否,1:是),",
    tenant              int comment ""
)
    row format delimited fields terminated by '\t'
    stored as orc tblproperties ("orc.compress" = "ZLIB")
;"
/usr/bin/sqoop import "-Dorg.apache.sqoop.sqlitter.allow_text_sqlitter=true" \
--connect 'jdbc:mysql://106.75.33.59:3306/scrm?useUnicode=true&characterEncoding=UTF8&autoReconnect=true' \
--username itcast_edu_stu \
--password itcast_edu_stu \
--query "select * from itcast_clazz where 1=1 and \$CONDITIONS" \
--hcatalog-database zx_ods \
--hcatalog-table ods_itcast_clazz_apply \
-m 1
wait

# 7.校区信息表
${HIVE_HOME} -S -e "drop table if exists zx_ods.ods_itcast_school_apply;
create table if not exists zx_ods.ods_itcast_school_apply
(
    id               int comment "",
    create_date_time string comment "创建时间'",
    update_date_time string comment "最后更新时间",
    deleted          int comment "'是否被删除（禁用）",
    name             string comment "校区名称'",
    code             string comment "",
    tenant           int comment ""
)
    row format delimited fields terminated by '\t'
    stored as orc tblproperties ("orc.compress" = "ZLIB")
;"
/usr/bin/sqoop import "-Dorg.apache.sqoop.sqlitter.allow_text_sqlitter=true" \
--connect 'jdbc:mysql://106.75.33.59:3306/scrm?useUnicode=true&characterEncoding=UTF8&autoReconnect=true' \
--username itcast_edu_stu \
--password itcast_edu_stu \
--query "select * from itcast_school where 1=1 and \$CONDITIONS" \
--hcatalog-database zx_ods \
--hcatalog-table ods_itcast_school_apply \
-m 1
wait

# 8.学科信息表
${HIVE_HOME} -S -e "drop table if exists zx_ods.ods_itcast_subject_apply;
create table if not exists zx_ods.ods_itcast_subject_apply
(
    id               int comment "",
    create_date_time string comment "创建时间",
    update_date_time string comment "最后更新时",
    deleted          int comment "是否被删除（禁用）",
    name             string comment "学科名称",
    code             string comment "",
    tenant           int comment ""
)
    row format delimited fields terminated by '\t'
    stored as orc tblproperties ("orc.compress" = "ZLIB")
;"
/usr/bin/sqoop import "-Dorg.apache.sqoop.sqlitter.allow_text_sqlitter=true" \
--connect 'jdbc:mysql://106.75.33.59:3306/scrm?useUnicode=true&characterEncoding=UTF8&autoReconnect=true' \
--username itcast_edu_stu \
--password itcast_edu_stu \
--query "select * from itcast_subject where 1=1 and \$CONDITIONS" \
--hcatalog-database zx_ods \
--hcatalog-table ods_itcast_subject_apply \
-m 1
wait

# 9.员工部门表
${HIVE_HOME} -S -e "drop table if exists zx_ods.ods_scrm_department_apply;
create table if not exists zx_ods.ods_scrm_department_apply
(
    id               int comment "",
    name             string comment "部门名称",
    parent_id        int comment "父部门id",
    create_date_time string comment "创建时间",
    update_date_time string comment "更新时间",
    deleted          int comment "删除标志",
    id_path          string comment "编码全路径",
    tdepart_code     int comment "直属部门",
    creator          string comment "创建者",
    depart_level     int comment "部门层级",
    depart_sign      int comment "部门标志暂时默认",
    depart_line      int comment "业务线存储业务线编码",
    depart_sort      int comment "排序字段",
    disable_flag     int comment "禁用标志",
    tenant           int comment ""
)
    row format delimited fields terminated by '\t'
    stored as orc tblproperties ("orc.compress" = "ZLIB")
;"
/usr/bin/sqoop import "-Dorg.apache.sqoop.sqlitter.allow_text_sqlitter=true" \
--connect 'jdbc:mysql://106.75.33.59:3306/scrm?useUnicode=true&characterEncoding=UTF8&autoReconnect=true' \
--username itcast_edu_stu \
--password itcast_edu_stu \
--query "select * from scrm_department where 1=1 and \$CONDITIONS" \
--hcatalog-database zx_ods \
--hcatalog-table ods_scrm_department_apply \
-m 1
wait


echo '========================================'
echo '=================success==============='
echo '========================================'